package qc.module.platform.service;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import qc.common.core.enums.ColumnTypeEnum;
import qc.common.core.exception.QCPromptException;
import qc.common.core.json.JsonParserUtil;
import qc.common.core.pair.KeyValuePairDto;
import qc.common.core.utils.SqlRunnerUtil;
import qc.common.core.utils.StringSplitUtil;
import qc.module.platform.dto.db.DBDto;
import qc.module.platform.dto.dbTable.DBTableDto;
import qc.module.platform.entity.QcDbTableColumn;

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据库表数据Service，提供公用的数据查询、保存方法
 *
 * @author QuCheng Tech
 * @since 2024/2/29
 */
@Service
public class DbTableDataService {
    private DbService dbService;
    private DbTableService dbTableService;
    private DbTableColumnService dbTableColumnService;
    private DictService dictService;

    @Autowired
    public void setDbService(DbService dbService) {
        this.dbService = dbService;
    }

    @Autowired
    public void setDbTableService(DbTableService dbTableService) {
        this.dbTableService = dbTableService;
    }

    @Autowired
    public void setDbTableColumnService(DbTableColumnService dbTableColumnService) {
        this.dbTableColumnService = dbTableColumnService;
    }

    @Autowired
    public void setDictService(DictService dictService) {
        this.dictService = dictService;
    }

    //自动根据数据库表查询列信息；在新增和修改时根据列信息对列进行赋值和有效性判断

    /***
     * 获取数据库表列的待选择数据集合：待选择数据可以来源于：序列、枚举、数据字典、对象集合
     *
     * @param coltype 列类型
     * @param valrange 取值范围
     * @return java.util.List<qc.common.core.pair.KeyValuePairDto>
     * @author QuCheng Tech
     * @since 2024/3/11
     */
    public List<KeyValuePairDto> getColumnOptions(ColumnTypeEnum coltype, String valrange) throws QCPromptException, SQLException {
        //遍历表单列进行处理：根据列类型判断：列类型为：对象ID/编码、对象名称、字典、枚举字典、数据字典
        //获取数据均需要有取值范围，取值范围为空时无法获取待选择数据集合
        if (StringUtils.isNotBlank(valrange)) {
            List<KeyValuePairDto> result = new ArrayList<>();

            //System.out.println("getColumnOptions coltype=" + coltype.getName() + " valrange=" + valrange);

            switch (coltype) {
                case OBJECT_ID:
                case OBJECT_NAME:
                    //从对应的数据库表编码中获取数据，注意需要带有对象参数信息
                    result = getObjects(valrange, true);
                    break;
                case DICT:
                    //从取值范围分解出集合
                    String[] strs = StringSplitUtil.Split(valrange, true);
                    if (strs != null && strs.length > 0x0) {
                        for (int i = 0;i < strs.length;i++){
                            if (strs[i].contains("=")){
                                String[] keyvalues = StringSplitUtil.Split(strs[i],"=");
                                for (int j = 0;j < keyvalues.length;j++){
                                    KeyValuePairDto pair = new KeyValuePairDto();
                                    pair.setKey(keyvalues[0]);
                                    pair.setValue(keyvalues[1]);
                                    result.add(pair);
                                }
                            }else {
                                //未指定key，默认从0开始编码
                                KeyValuePairDto pair = new KeyValuePairDto();
                                pair.setKey(String.valueOf(i));
                                pair.setValue(strs[i]);
                                result.add(pair);
                            }
                        }
                    }
                    
                    break;
                case DICT_ENUM:
                    //从枚举类中获取key和value// 假设你有一个枚举类的全类名
                    String enumClassName = valrange;
                    try {
                        // 使用Class.forName()获取Class对象
                        Class<?> enumClass = Class.forName(enumClassName);
                        // 确保这是一个枚举类型
                        if (enumClass.isEnum()) {
                            Class<Enum> ecs = (Class<Enum>) enumClass;
                            // 获取所有枚举常量
                            Object[] enumConstants = enumClass.getEnumConstants();
                            // 遍历所有常量
                            for (Object constant : enumConstants) {
                                //constant是枚举的每个元素，toString的结果是英文名称，如果重写了枚举类中的toString方法得到的是重写后的
                                //System.out.println("constant=" + constant.toString());
                                //获取枚举类的name
                                Field nameField = constant.getClass().getDeclaredField("name");
                                nameField.setAccessible(true);
                                //获取枚举类的index
                                Field indexField = constant.getClass().getDeclaredField("index");
                                indexField.setAccessible(true);

                                if (nameField != null && indexField != null) {
                                    KeyValuePairDto pair = new KeyValuePairDto();
                                    pair.setKey(indexField.get(constant).toString());
                                    pair.setValue(nameField.get(constant).toString());

                                    result.add(pair);
                                }
                            }
                        } else {
                            throw new IllegalArgumentException(enumClassName + " is not an enum class");
                        }
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    } catch (NoSuchFieldException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    break;
                case DICT_DEFINE:
                    //从数据字典中获取key和value
                    result = dictService.getCategoryValidValues(valrange);
                    break;
                default:
                    break;
            }

            if (result != null && result.size() > 0x0)
                return result.stream().distinct().collect(Collectors.toList());//去重
        }

        return null;
    }

    /***
     * 根据指定的数据库表编码获取对象集合信息，自动根据数据库表列进行查询获取
     *
     * @param tableCode 数据库表编码
     * @param onlyValid 是否只为状态有效对象
     * @return java.util.List<qc.common.core.pair.KeyValuePairDto>
     * @author QuCheng Tech
     * @since 2024/3/10
     */
    public List<KeyValuePairDto> getObjects(String tableCode, boolean onlyValid) throws QCPromptException, SQLException {
        if (StringUtils.isNotBlank(tableCode)) {
            //不使用ParamValueService中的方法，ParamValueService中的方法用于从查询参数值结果中获取对应的列信息
            //获取数据库表信息，如果数据库表信息不存在无法进行后继操作
            DBTableDto dbTable = dbTableService.get(tableCode);
            if (dbTable != null) {
                //获取数据库信息，如果数据库信息不存在无法进行后继操作
                DBDto db = dbService.get(dbTable.getDb());
                if (db != null) {
                    //获取数据库表列信息，如果列信息不存在无法构造查询sql语句，无法进行后继操作
                    List<QcDbTableColumn> columns = dbTableColumnService.getTableColumnEntitys(tableCode, false,"qrodr");
                    if (columns != null && columns.size() > 0x0) {
                        //验证数据库表列信息中至少有：ID、名称；可选列：参数值
                        String idColumnName = null;
                        String nameColumnName = null;
                        String paramColumnName = null;
                        String statusColumnName = null;
                        Optional<QcDbTableColumn> idColumn = columns.stream().filter(p -> p.getColtype() == ColumnTypeEnum.OBJECT_ID).findFirst();
                        Optional<QcDbTableColumn> nameColumn = columns.stream().filter(p -> p.getColtype() == ColumnTypeEnum.OBJECT_NAME).findFirst();
                        Optional<QcDbTableColumn> paramColumn = columns.stream().filter(p -> p.getColtype() == ColumnTypeEnum.PARAM_VALUE).findFirst();
                        Optional<QcDbTableColumn> statusColumn = columns.stream().filter(p -> p.getColtype() == ColumnTypeEnum.STATUS_RESOURCE).findFirst();
                        if (idColumn != null && idColumn.isPresent())
                            idColumnName = idColumn.get().getCode();
                        if (nameColumn != null && nameColumn.isPresent())
                            nameColumnName = nameColumn.get().getCode();
                        if (paramColumn != null && paramColumn.isPresent())
                            paramColumnName = paramColumn.get().getCode();
                        if (statusColumn != null && statusColumn.isPresent())
                            statusColumnName = statusColumn.get().getCode();

                        if (StringUtils.isNotBlank(idColumnName)) {
                            if (StringUtils.isNotBlank(nameColumnName)) {
                                //构造查询语句
                                StringBuilder sbSql = new StringBuilder("select " + idColumnName + "," + nameColumnName + (StringUtils.isNotBlank(paramColumnName) ? "," + paramColumnName : "") + " from " + tableCode);
                                //如果有状态列，并且指定的只是有效状态，添加where条件
                                if (StringUtils.isNotBlank(statusColumnName) && onlyValid) {
                                    sbSql.append(" where " + statusColumnName + "=0");
                                }
                                //排序，根据列集合自动处理
                                List<QcDbTableColumn> orderColumns = columns.stream().filter(p -> p.getQrascorder() != null).sorted(Comparator.comparingInt(QcDbTableColumn::getQrorderidx)).collect(Collectors.toList());
                                if (orderColumns != null && orderColumns.size() > 0x0) {
                                    sbSql.append(" order by ");
                                    for (QcDbTableColumn column : orderColumns) {
                                        if (column.getQrascorder())
                                            sbSql.append(column.getCode() + " asc,");
                                        else
                                            sbSql.append(column.getCode() + " desc,");
                                    }
                                    //去除最后的逗号,
                                    sbSql.setLength(sbSql.length() - 1);
                                }

                                String dbUrl = "";
                                String dbUser = "";
                                String dbPassword = "";
                                //解析数据库连接信息
                                String dbConJsonString = db.getCon();
                                if (StringUtils.isNotBlank(dbConJsonString)) {
                                    //Json对象进行获取
                                    dbUrl = JsonParserUtil.getString(dbConJsonString, "url");
                                    dbUser = JsonParserUtil.getString(dbConJsonString, "user");
                                    dbPassword = JsonParserUtil.getString(dbConJsonString, "password");
                                }
                                //执行sql查询
                                List<Map<String, Object>> queryResult = SqlRunnerUtil.executeSelectListSql(dbUrl, dbUser, dbPassword, sbSql.toString());
                                if (queryResult != null && queryResult.size() > 0x0) {
                                    List<KeyValuePairDto> result = new ArrayList<>();

                                    for (Map<String, Object> row : queryResult) {
                                        String objId = row.get(idColumnName).toString();
                                        if (StringUtils.isNotBlank(objId)) {
                                            String objName = row.get(nameColumnName).toString();

                                            KeyValuePairDto obj = new KeyValuePairDto();
                                            obj.setKey(objId);
                                            obj.setValue(objName);
                                            if (StringUtils.isNotBlank(paramColumnName) && row.get(paramColumnName) != null)
                                                obj.setAttribute(row.get(paramColumnName).toString());

                                            result.add(obj);
                                        }
                                    }

                                    return result;
                                }
                            } else {
                                System.out.println("DbTableDataService 根据表编码：" + tableCode + " 获取对象集合信息，ID列不存在");
                            }
                        } else {
                            System.out.println("DbTableDataService 根据表编码：" + tableCode + " 获取对象集合信息，Name列不存在");
                        }
                    }
                } else {
                    //获取数据库信息为空
                    System.out.println("DbTableDataService 根据表编码：" + tableCode + " 获取对象集合信息，数据库信息为空");
                }
            } else {
                //获取数据库表信息为空
                System.out.println("DbTableDataService 根据表编码：" + tableCode + " 获取对象集合信息，数据库表信息为空");
            }
        }

        return null;
    }
}
